package DAO;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import Negocio2.Aluno;

public class AlunoDAO  {
	private Connection conexao;
	public AlunoDAO() throws SQLException{
		this.conexao = CriaConexao.getConexao();
	}
	public void adiciona(Aluno aluno)throws SQLException{
		
		//Prepara conex�o
		String sql = "insert into alunos(nome,matricula,endereco,contato)"+
		"values(?,?,?,?)";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		
		
		//Seta os valores
		stmt.setString(1, aluno.getNome());
		stmt.setString(2, aluno.getMatricula());
		stmt.setString(3, aluno.getEndereco());
		stmt.setString(4, aluno.getContato());
		
		
		//Executa o c�digo SQL
		stmt.execute();
		stmt.close();
	}
	
	public List<Aluno> getLista() throws SQLException{
		String sql = "select * from alunos";
		PreparedStatement stmt = this.conexao.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		
		List<Aluno> listaAlunos = new ArrayList<Aluno>();
		
		while(rs.next()){
			Aluno aluno = new Aluno();
			aluno.setNome(rs.getString("nome"));
			aluno.setMatricula(rs.getString("matricula"));
			aluno.setEndereco(rs.getString("endereco"));
			aluno.setContato(rs.getString("contato"));
			listaAlunos.add(aluno);
		}
		rs.close();
		stmt.close();
		return listaAlunos;
	}
	
	public void altera(Aluno aluno) throws SQLException{
		String sql = "update alunos set nome=?, matricula=?, endereco=?, contato=? where matricula=?";
		PreparedStatement stmt = conexao.prepareStatement(sql);	
		
		//Seta os valores
		stmt.setString(1, aluno.getNome());
		stmt.setString(2, aluno.getMatricula());
		stmt.setString(3, aluno.getEndereco());
		stmt.setString(4, aluno.getContato());
		stmt.setString(5, aluno.getMatricula());
		
		//Executa o c�digo SQL
		stmt.execute();
		stmt.close();
	}
	
	public void remove(Aluno aluno) throws SQLException{
		String sql = "delete from alunos where matricula =?";
		PreparedStatement stmt = conexao.prepareStatement(sql);
		stmt.setString(1, aluno.getMatricula());
		stmt.execute();
		stmt.close();
	}
}
